﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace ADO.NETExercise
{
    public class Enchancements
    {
        public const string CONNECTION_STRING_NORTHWIND = "Server=.\\SQLEXPRESS; database=Northwind; Integrated Security = SSPI";
        private static SqlConnection dbConnection;

        private static void OpenConnection(string connectionString)
        {
            dbConnection = new SqlConnection(CONNECTION_STRING_NORTHWIND);
            dbConnection.Open();
        }

        private static void CloseConnection()
        {
            dbConnection.Close();
        }

        private static List<int> ExtractProductCategoriesIDs()
        {
            OpenConnection(CONNECTION_STRING_NORTHWIND);
            //SqlConnection dbConnection = new SqlConnection(CONNECTION_STRING_NORTHWIND);
            List<int> ids = new List<int>();

            
            using(dbConnection)
            {
                SqlCommand extractIDsQuery = new SqlCommand(
                    "Select CategoryId From Categories", dbConnection);

                SqlDataReader reader = extractIDsQuery.ExecuteReader();
                using (reader)
                {
                    while(reader.Read())
                    {
                        int categorie = (int)reader["CategoryId"];

                        ids.Add(categorie);
                    }
                }
            }
            CloseConnection();

            return ids;
        }

        private static List<string> ExtractProductsInCategorie(int categoryID)
        {
            List<string> names = new List<string>();

            SqlCommand extractNamesQuery = new SqlCommand(
                "Select ProductName from Products where CategoryID = @categoryId", dbConnection);
            SqlParameter categoryParameter = new SqlParameter("@categoryId", categoryID);
            extractNamesQuery.Parameters.Add(categoryParameter);

            SqlDataReader reader = extractNamesQuery.ExecuteReader();
            using (reader)
            {
                while(reader.Read())
                {
                    string name = (string)reader["ProductName"];
                    names.Add(name);
                }
            }

            return names;
        }

        private static string GetCategoryNameByID(int id)
        {
            SqlCommand getName = new SqlCommand(
                "Select CategoryName from Categories where CategoryId=@id", dbConnection);
            SqlParameter idParam = new SqlParameter("@id", id);
            getName.Parameters.Add(idParam);

            SqlDataReader reader = getName.ExecuteReader();
            string name = null;
            using (reader)
            {
                if (reader.Read())
                {
                    name = (string)reader["CategoryName"];
                }
            }

            return name;
        }

        public static void PrintCategoriesWithProducts()
        {
            List<int> categorieIds = ExtractProductCategoriesIDs();

            OpenConnection(CONNECTION_STRING_NORTHWIND);
            //SqlConnection dbCon = new SqlConnection(CONNECTION_STRING_NORTHWIND);
            
            using(dbConnection)        
            {
                foreach (int id in categorieIds)
	            {
		            string categoryName = GetCategoryNameByID(id);
                    List<string> products = ExtractProductsInCategorie(id);

                    Console.WriteLine(categoryName + ":");
                    Console.WriteLine();

                    foreach (string item in products)
                    {
                        Console.WriteLine(item + " ");
                    }
                    Console.WriteLine(new string('-', 50));
	            }
            }

            CloseConnection();
        }

        public static int AddProduct(string productName, int? supplierId, int? categoryId, string quantityPerUnit,
            decimal? unitPrice, int? unitsInStock, int? unitsInOrder, int? recordLevel, bool discontinued)
        {
            OpenConnection(CONNECTION_STRING_NORTHWIND);

            SqlCommand cmdInsertProduct = new SqlCommand(
                "Insert into Products(ProductName, SupplierId, CategoryId, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) "+
                "Values (@name, @supplier, @category, @quantity, @unitPr, @unitsInSt, @unitsOrd, @recordlvl, @disc)", dbConnection);

            #region parameters
            cmdInsertProduct.Parameters.Add(new SqlParameter("@name", productName));
            SqlParameter supplierIdParam = new SqlParameter("@supplier", supplierId);
            if (supplierId == null)
            {
                supplierIdParam.Value = DBNull.Value;
            }
            cmdInsertProduct.Parameters.Add(supplierIdParam);

            SqlParameter categoryIdParam = new SqlParameter("@category", categoryId);
            if (categoryId == null)
            {
                categoryIdParam.Value = DBNull.Value;
            }
            cmdInsertProduct.Parameters.Add(categoryIdParam);

            SqlParameter quantityParam = new SqlParameter("@quantity", quantityPerUnit);
            if (quantityPerUnit == null)
            {
                quantityParam.Value = DBNull.Value;
            }
            cmdInsertProduct.Parameters.Add(quantityParam);

            SqlParameter unitPrParam = new SqlParameter("@unitPr", unitPrice);
            if (unitPrice == null)
            {
                unitPrParam.Value = DBNull.Value;
            }
            cmdInsertProduct.Parameters.Add(unitPrParam);

            SqlParameter unitsStParam = new SqlParameter("@unitsInSt", unitsInStock);
            if (unitsInStock == null)
            {
                unitsStParam.Value = DBNull.Value;
            }
            cmdInsertProduct.Parameters.Add(unitsStParam);

            SqlParameter unitsInOrderParam = new SqlParameter("@unitsOrd",unitsInOrder);
            if (unitsInOrder == null)
            {
                unitsInOrderParam.Value = DBNull.Value;
            }
            cmdInsertProduct.Parameters.Add(unitsInOrderParam);
            
            SqlParameter recordLevelParam = new SqlParameter("@recordlvl", recordLevel);
            if (recordLevel == null)
            {
                recordLevelParam.Value = DBNull.Value;
            }
            cmdInsertProduct.Parameters.Add(recordLevelParam);

            cmdInsertProduct.Parameters.Add(new SqlParameter("@disc", discontinued));

            #endregion

            cmdInsertProduct.ExecuteNonQuery();
            
            //SqlCommand cmdSelectIdentity =
            //new SqlCommand("SELECT @@Identity", dbConnection);
            //int insertedRecordId =
            //    (int)(decimal)cmdSelectIdentity.ExecuteScalar();
            //CloseConnection();
            return 0;// insertedRecordId;
        }
    }
}
